package DbManager;
// test
import java.io.*; 
import java.net.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.regex.Pattern;

public class DbManager1 
{
	public Connection connect = null;
	public Statement statement = null;
	public PreparedStatement preparedStatement = null;
	public ResultSet resultSet = null;
	
	public void connect_to_database() throws Exception
	{
	    try {
	      // this will load the MySQL driver, each DB has its own driver
	      Class.forName("com.mysql.jdbc.Driver");
	      // setup the connection with the DB.
	      connect = DriverManager.getConnection("jdbc:mysql://localhost/JoinMeDb","root","1234");

	      // statements allow to issue SQL queries to the database
	      statement = connect.createStatement();
	      
	    }
	    catch(SQLException e)
		{
			throw e;
		}
	    catch (Exception e)
	    {
	      throw e;
	    }
	    
	}
	
	public void close_connection() throws Exception
	{
		try
		{
			connect.close();
	    	statement.close();
	    	preparedStatement.close();
		}
		catch(SQLException e)
		{
			throw e;
		}
		catch (Exception e)
	    {
	      throw e;
	    }
		
		
	}
	
	/*
	 * Validation for passwords 
	 */
	public boolean is_valid( String pass )
	{	
		if( pass.length() > 4 && pass.length() < 16 )
			return true;
		
		return false;
	}
	
	/*
	 * Login
	 * Input: user, pass
	 * Return Bool
	 * 
	 * */
	public boolean login( String user, String pass) throws Exception
	{
		if( !is_valid(pass) )
			return false;
		
		try
		{
			preparedStatement = connect.prepareStatement("SELECT username, Password FROM `Users` WHERE username=? AND Password = ? LIMIT 1;");
			preparedStatement.setString(1, user);
			preparedStatement.setString(2, pass);
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				if( resultSet.getString("username").equals(user)  && resultSet.getString("Password").equals(pass))
					return true;
			}
			
			return false;
		
		}
		catch(SQLException e)
		{
		    
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	
	
	/*
	 * Return id for user details
	 * */
	public void init_insert_preferences( int idUser ) throws Exception
	{	
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO `Preferences` (Users_IdUser) VALUES(?)");
			preparedStatement.setInt(1, idUser);
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
		    
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
    
	}
	
	public void init_insert_user_details(int idPreference, String IMEI, int idAccounts,int idUser ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO `User_Detail` (Id_Preference, IMEI, idAccounts, Users_IdUser ) VALUES(?,?,?,?);");
			preparedStatement.setInt(1, idPreference);
			preparedStatement.setString(2, IMEI);
			preparedStatement.setInt(3, idAccounts);
			preparedStatement.setInt(4, idUser);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
		    
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	    
	}
	
	
	/*
	 * Input: username
	 * Output: username id
	 * if user was not found, returns 0
	 * */
	public int get_id( String user ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("SELECT idUser FROM `Users` WHERE username=? LIMIT 1;");
			preparedStatement.setString(1, user);
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{	
					return resultSet.getInt("idUser");
			}
			
			return 0;
		}
		catch(SQLException e)
		{
		    
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	/*
	 * Get details id by username
	 * */
	public int get_details_id( String user ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("SELECT User_Detail_idUser FROM `Users` WHERE username=? LIMIT 1;");
			preparedStatement.setString(1, user);
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{	
					return resultSet.getInt("User_Detail_idUser");
			}
			
			return 0;
		}
		catch(SQLException e)
		{
		    
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	/*
	 * Registration
	 * 
	 *
	 **/
	public boolean valid_user( String user ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("SELECT username FROM `Users` WHERE username=? LIMIT 1;");
			preparedStatement.setString(1, user);
			
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				if( resultSet.getString("username").equals(user) )
					return false;
			}
			
			return true;
		
		}
		catch(SQLException e)
		{
		    
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	
	}
	
	public boolean register( String user, String pass, String Question, String Answer, String IMEI ) throws Exception
	{
		if( !is_valid(pass) || !valid_user(user))
			return false;
		
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO `Users` (username, Password) VALUES(?, ?);");
			preparedStatement.setString(1, user);
			preparedStatement.setString(2, pass);
			
			preparedStatement.executeUpdate();
			
			
			/*
			 * Get user id
			 * 
			 * */
			int idUser = get_id( user );
			// Debug
			System.out.println("User id = "+idUser);
			
			if( idUser == 0 )
			{
				System.out.println("Error: User id == 0 ");
				return false;
			}
			
			// init for user preferences
			init_insert_preferences(idUser);

			// init for user filter
			init_insert_filter(idUser);
			
			// init social account
			init_insert_social_account(idUser);

			// init user status 
			init_insert_user_status( idUser );

			// init insert interest
			init_inser_interest( idUser );

			int idPreference = get_last_idPreference( idUser );
			int idAccounts = get_last_idAccounts( idUser );
			
			init_insert_user_details(idPreference, IMEI, idAccounts, idUser );
			
			// init for authentication
			preparedStatement = connect.prepareStatement("INSERT INTO `Authentication` (SecurityQuestion, SecurityAnswer, User_idUser) VALUES(?, ?, ?);");
			preparedStatement.setString(1, Question);
			preparedStatement.setString(2, Answer);
			preparedStatement.setInt(3, idUser);
			
			
			preparedStatement.executeUpdate();
			
			
			
			return true;
		
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
		
	}
	
	
	/*
	 * Users interest
	 */
	public void init_inser_interest( int idUser ) throws Exception
	{
		try
		{
			// init for authentication
			preparedStatement = connect.prepareStatement("INSERT INTO `Interest` (_idUser) VALUES(?);");
			preparedStatement.setInt(1, idUser);

			preparedStatement.executeUpdate();

		}
		catch(SQLException e)
		{

			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	/*
	 * Status functions
	 */
	public void init_insert_user_status( int id ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO `UserStatus`(Users_idUser) VALUES(?)");
			preparedStatement.setInt(1, id);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	public void update_user_status( int id, String status, String latitude, String longitude, String locationName) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("UPDATE `UserStatus` SET Status=?, Latitude=?, Longitude=?, LocationName=? WHERE Users_idUser=?");
			preparedStatement.setString(1, status);
			preparedStatement.setString(2, latitude);
			preparedStatement.setString(3, longitude);
			preparedStatement.setString(4, locationName);
			preparedStatement.setInt(5, id);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	/*
	 * Verify if user details exist, if not insert blank
	 * */
	public boolean is_user_detail( int idUser ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("SELECT idUser FROM `Users` WHERE idUser=? LIMIT 1;");
			preparedStatement.setInt(1, idUser);
			
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				if( resultSet.getString("idUser").equals(idUser) )
					return true;
			}
			
			return false;
		
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	
	/*
	 * Update user details (!important - Photo ar trebui sa fie VARCHAR )
	 * 
	 */
	public void delete_settings( int Users_idUser ) throws Exception
	{
		try
		{
				preparedStatement = connect.prepareStatement("DELETE FROM `User_Detail` WHERE Users_IdUser=? LIMIT 1");
				preparedStatement.setInt(1, Users_idUser);
				
				preparedStatement.executeUpdate();
			
		}
		catch(SQLException e)
		{
		    
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	// Blob Photo.toString();
	public void update_settings(int idUser, String Name, String Sex, String Email, String Location, String IMEI, String Photo, String PhoneNumber) throws Exception
	{
		try
		{
				
					preparedStatement = connect.prepareStatement("UPDATE `User_Detail` SET Name=?, Sex=?, Email=?, Location=?, IMEI=?, Photo=?, PhoneNumber=? WHERE Users_IdUser=? LIMIT 1");
					preparedStatement.setString(1, Name);
					preparedStatement.setString(2, Sex);
					preparedStatement.setString(3, Email);
					preparedStatement.setString(4, Location);
					preparedStatement.setString(5, IMEI);
					preparedStatement.setString(6, Photo);
					preparedStatement.setString(7, PhoneNumber);
					preparedStatement.setInt(8, idUser);
					
					preparedStatement.executeUpdate();
				
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	
	/*
	 * Friend options
	 * 
	 * */
	
	/*
	 * Get list of ids of friends
	 * 
	 * */
	public ArrayList<Integer> get_friends_ids_list( int idUser ) throws Exception
	{
		ArrayList<Integer> a = new ArrayList<Integer>();
		try
		{
			preparedStatement = connect.prepareStatement("SELECT Friend FROM `Friends` WHERE Users_idUser=? ;");
			preparedStatement.setInt(1, idUser);
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				a.add(resultSet.getInt("Friend"));
			}
			
			return a;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	/*
	 * Return arraylist
	 * 
	 * */
	public ArrayList<String> get_user_detail( int idUser ) throws Exception
	{
		ArrayList<String> a = new ArrayList<String>();
		
		try
		{
			preparedStatement = connect.prepareStatement("SELECT Name,Sex,Email,Location,IMEI,Photo,PhoneNumber FROM `User_Detail` WHERE Users_idUser=? LIMIT 1;");
			preparedStatement.setInt(1, idUser);
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				a.add(resultSet.getString("Name"));
				a.add(resultSet.getString("Sex"));
				a.add(resultSet.getString("Email"));
				a.add(resultSet.getString("Location"));
				a.add(resultSet.getString("IMEI"));
				a.add(resultSet.getString("Photo"));
				a.add(resultSet.getString("PhoneNumber"));
			}
			
			return a;
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	/*
	 * Return details of all friends
	 * */
	public ArrayList<ArrayList<String>> get_friends_details( int idUser ) throws Exception
	{
		ArrayList<ArrayList<String>> a = new ArrayList<ArrayList<String>>();
		
		try
		{
			preparedStatement = connect.prepareStatement("SELECT Friend FROM `Friends` WHERE Users_idUser=? ;");
			preparedStatement.setInt(1, idUser);
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				a.add( get_user_detail(resultSet.getInt("Friend")) );
				
			}
			
			return a;
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
		
	}
	
	public ArrayList<String> get_friends_details2( int idUser ) throws Exception
	{
		ArrayList<String> a = new ArrayList<String>();
		
		try
		{
			preparedStatement = connect.prepareStatement("SELECT ud.Name,ud.Sex,ud.Email,ud.Location,ud.Photo,ud.PhoneNumber FROM `User_Detail` ud join Friends f WHERE f.Friend = ud.idUser and f.Users_idUser=? LIMIT 1;");
			preparedStatement.setInt(1, idUser);
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				a.add(resultSet.getString("Name"));
				a.add(resultSet.getString("Sex"));
				a.add(resultSet.getString("Email"));
				a.add(resultSet.getString("Location"));
				//a.add(resultSet.getString("IMEI"));
				a.add(resultSet.getString("Photo"));
				a.add(resultSet.getString("PhoneNumber"));
			}
			
			return a;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	public void add_friend( int idUser, int idFriend ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO `Friends` (Friend, Users_idUser) VALUES(?, ?);");
			preparedStatement.setInt(1, idFriend);
			preparedStatement.setInt(2, idUser);
			preparedStatement.executeUpdate();
			
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	public void remove_friend( int idUser, int idFriend ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("DELETE FROM `Friends` WHERE Users_idUser=? AND Friend=? ;");
			preparedStatement.setInt(1, idUser);
			preparedStatement.setInt(2, idFriend);
			preparedStatement.executeUpdate();
			
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	
	/*
	 * Group queries
	 * 
	 * */
	public ArrayList<String> get_group_details( int idGroup ) throws Exception
	{
		ArrayList<String> a = new ArrayList<String>();
		
		try
		{
			preparedStatement = connect.prepareStatement("SELECT GroupName,GroupPic,Groupcol FROM `Group` WHERE idGroup=? LIMIT 1;");
			preparedStatement.setInt(1, idGroup);
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				a.add(resultSet.getString("GroupName"));
				a.add(resultSet.getString("GroupPic"));
				a.add(resultSet.getString("Groupcol"));
			}
			
			return a;
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	/*
	 * Check if GroupName was taken
	 * */
	public boolean valid_group( String GroupName ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("SELECT GroupName FROM `Group` WHERE GroupName=? LIMIT 1;");
			preparedStatement.setString(1, GroupName);
			
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				if( resultSet.getString("GroupName").equals(GroupName) )
					return false;
			}
			
			return true;
		
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	public boolean create_group(String GroupName, String GroupPic ) throws Exception
	{
		if(!valid_group(GroupName))
			return false;
		
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO `Group`(GroupName,GroupPic) VALUES(?,?) ;");
			preparedStatement.setString(1, GroupName);
			preparedStatement.setString(2, GroupPic);
			
			preparedStatement.executeUpdate();
			
			return true;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	public void join_group( int Group_idGroup, int Users_idUser ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO Group_has_Users(Group_idGroup, Users_idUser) VALUES(?,?) ;");
			preparedStatement.setInt(1, Group_idGroup);
			preparedStatement.setInt(2, Users_idUser);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	/*
	 * Delete group
	 * First remove all users from group, then delete group 
	 * */
	public void delete_group( int idGroup ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("DELETE FROM Group_has_Users WHERE Group_idGroup=? ;");
			preparedStatement.setInt(1, idGroup);
			
			preparedStatement.executeUpdate();
			
			preparedStatement = connect.prepareStatement("DELETE FROM `Group` WHERE idGroup=? ;");
			preparedStatement.setInt(1, idGroup);
			
			preparedStatement.executeUpdate();
			
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	public void update_group( int idGroup, String GroupName, String GroupPic ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("UPDATE `Group` SET GroupName=?, GroupPic=? WHERE idGroup=? LIMIT 1;");
			preparedStatement.setString(1, GroupName);
			preparedStatement.setString(2, GroupPic);
			preparedStatement.setInt(3, idGroup);
			
			preparedStatement.executeUpdate();
		}
		catch(Exception e)
		{
			throw e;
		}
	}
	
	
	
	/*
	 * Update user preferences
	 */
	
	public int get_last_idAccounts( int idUser) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("SELECT idAccounts FROM Social_Accounts WHERE _idUser=? LIMIT 1;");
			preparedStatement.setInt(1, idUser);
		
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next())
			{	
				// for each received message, update ReciveTimestamp
				return resultSet.getInt("idAccounts");
			}
			
			// exception return 0
			return 0;
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	
	public int get_last_idPreference( int idUser) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("SELECT IdUser FROM Preferences WHERE Users_IdUser=? LIMIT 1;");
			preparedStatement.setInt(1, idUser);
		
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next())
			{	
				// for each received message, update ReciveTimestamp
				return resultSet.getInt("IdUser");
			}
			
			// exception return 0
			return 0;
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	public void update_user_preferences( int IdUser, String Location, String Age, int Radius, String Idle_Time, int MaxUser) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("UPDATE Preferences SET Location=?,Age=?,Radius=?,Idle_Time=?,MaxUser=? WHERE Users_idUser=? LIMIT 1;");
			preparedStatement.setString(1, Location);
			preparedStatement.setString(2, Age);
			preparedStatement.setInt(3, Radius);
			preparedStatement.setString(4, Idle_Time);
			preparedStatement.setInt(5, MaxUser);
			preparedStatement.setInt(6, IdUser);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	
	/*
	 * Search functions
	 * */
	public ArrayList<String> search_by_username( String username ) throws Exception
	{
		ArrayList<String> a = new ArrayList<String>();
		
		//get_user_detail
		try
		{
			int id = get_details_id( username );
		
			return get_user_detail(id);
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	public ArrayList<String> search_by_username2( String username ) throws Exception
	{
		ArrayList<String> a = new ArrayList<String>();
		
		try
		{
			preparedStatement = connect.prepareStatement("SELECT ud.Name,ud.Sex,ud.Email,ud.Location,ud.Photo,ud.PhoneNumber FROM `User_Detail` ud, Friends f, Users u WHERE f.Friend = ud.idUser and f.Users_idUser= u.idUser and usename=? LIMIT 1;");
			preparedStatement.setString(1, username);
			
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next())
			{
				a.add(resultSet.getString("Name"));
				a.add(resultSet.getString("Sex"));
				a.add(resultSet.getString("Email"));
				a.add(resultSet.getString("Location"));
				//a.add(resultSet.getString("IMEI"));
				a.add(resultSet.getString("Photo"));
				a.add(resultSet.getString("PhoneNumber"));
			}
			
			return a;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	/*
	 * Filter Functions
	 * 
	 */
	public void init_insert_filter( int idUser) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO `Filters` (Users_idUser) VALUES(?)");
			preparedStatement.setInt(1, idUser);
			preparedStatement.executeUpdate();
			
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	public void delete_filter( int Users_idUser ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("DELETE FROM `Filters` WHERE Users_idUser =?");
			preparedStatement.setInt(1, Users_idUser);
			
			preparedStatement.executeUpdate();
			
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	public void update_filter( int Users_idUser, String FilterType, String FilterValue, String FilterState ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("UPDATE `Filters` SET FilterType=?, FilterValue=?, FilterState=?, LastUpdate=CURDATE() WHERE Users_idUser =? LIMIT 1");
			preparedStatement.setString(1, FilterType);
			preparedStatement.setString(2, FilterValue);
			preparedStatement.setString(3, FilterState);
			preparedStatement.setInt(4, Users_idUser);
			
			preparedStatement.executeUpdate();
			
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	
	/*
	 * Message Manager
	 * 
	 * */
	
	// when message was read
	public void update_timestamp_message( int idConversation ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("UPDATE Conversation SET ReciveTimestamp=CURRENT_TIMESTAMP() WHERE idConversation=? ;");
			preparedStatement.setInt(1, idConversation);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch(Exception e)
		{
			throw e;
		}
	}
	
	public ArrayList<Object> list_received_messages( int idUser ) throws Exception
	{
		ArrayList<Object> a = new ArrayList<Object>();
		
		try
		{
			preparedStatement = connect.prepareStatement("SELECT idConversation,SenderId,Message FROM Conversation WHERE ReceiverId=? ORDER BY SendTimestamp DESC;");
			preparedStatement.setInt(1, idUser);
			
			
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next())
			{	
				// for each received message, update ReciveTimestamp
				update_timestamp_message(resultSet.getInt("idConversation"));
				
				a.add(resultSet.getInt("idConversation"));
				a.add(resultSet.getInt("SenderId"));
				a.add(resultSet.getString("Message"));
			}
			
			return a;
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch(Exception e)
		{
			throw e;
		}
	}
	
	public void delete_message( int idConversation ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("DELETE FROM Conversation WHERE idConversation=? ;");
			preparedStatement.setInt(1, idConversation);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch(Exception e)
		{
			throw e;
		}
	}
	
	public void send_message( int idUser, int idTo, int Users_IdUser, String message, String Status, int Push ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO Conversation(SenderId,ReceiverId,Time,Message,Users_idUser,SendTimestamp, ReciveTimestamp, Status, NeedsPush) VALUES(?,?,CURRENT_TIMESTAMP(),?,?,CURRENT_TIMESTAMP(),0,?,?);");
			preparedStatement.setInt(1, idUser);
			preparedStatement.setInt(2, idTo);
			preparedStatement.setString(3, message);
			preparedStatement.setInt(4, Users_IdUser);
			preparedStatement.setString(5, Status);
			preparedStatement.setInt(6, Push);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch (Exception e)
		{
			throw e;
		}
	}
	
	/*
	 * Social Account
	 * 
	 */
	public void init_insert_social_account( int idUser ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("INSERT INTO Social_Accounts(_idUser) VALUES(?) ;");
			preparedStatement.setInt(1, idUser);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	public void update_social_account( int idAccounts, String AccountType, String AccountValues, String AccountState ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("UPDATE Social_Accounts SET AccountType=?, AccountValues=?, AccountState=?, LastUpdate=CURDATE() WHERE idAccounts=? LIMIT 1;");
			preparedStatement.setString(1, AccountType);
			preparedStatement.setString(2, AccountValues);
			preparedStatement.setString(3, AccountState);
			
			preparedStatement.setInt(4, idAccounts);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	public void delete_social_account( int idUser ) throws Exception
	{
		try
		{
			preparedStatement = connect.prepareStatement("DELETE FROM Social_Accounts WHERE _idUser=? ;");
			preparedStatement.setInt(1, idUser);
			
			preparedStatement.executeUpdate();
		}
		catch(SQLException e)
		{
			
			throw e;
		}
		catch( Exception e )
		{
			throw e;
		}
	}
	
	  
	
	public static void main(String args[]) throws Exception 
    { 
	     try
	     {
	    	 DbManager1 db = new DbManager1();
	    	 db.connect_to_database();
 
	    	 //System.out.println("DbManager connected");	    	 
	    	 db.close_connection();
	     }
	     
	     catch(SQLException e)
	     {
				System.out.println("SQL exception: "+e.getMessage());
				System.exit(1);
	     }
	     catch (Exception e)
	     {
	    	 System.out.println("DbManager is occupied.");
	    	 System.exit(1);
	     }
    }
}  